'''
Created on Oct 29, 2012

@author: root

Put all the SQL statements in this file
'''

###################Authentication############################
SQL_INSERT_USER = """
        INSERT INTO Users 
        (fb_user_id, first_name, last_name, user_pic, user_pic_square, birthday, age, gender, location_hometown, location_current) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """
        
SQL_INSERT_USER_AUTH = """
        INSERT INTO UserAuth
        (user_id, device_id, device_type_id, active_status) 
        VALUES (?, ?, ?, ?)
        """
        
SQL_INSERT_USER_MANAGEMENT = """
        INSERT INTO UserManagement
        (user_id, fb_user_id, fb_token, fb_token_status) 
        VALUES (?, ?, ?, ?)
        """                

SQL_SEL_USER_MANAGEMENT = """
        SELECT user_id, fb_user_id, fb_token, fb_token_status 
        FROM UserManagement WHERE fb_user_id = ?        
        """
        
SQL_SEL_FB_FRIENDS = """
        SELECT fb_friend_id
        FROM FacebookFriends WHERE fb_user_id = ?        
        """   

SQL_SEL_FB_USER = """
        SELECT fb_user_id, user_name, mail_name, user_pic_url
        FROM FacebookUsers WHERE fb_user_id = ?        
        """             
        
SQL_SEL_USER_INFO = """
        SELECT user_id, fb_user_id, first_name, last_name, user_pic, user_pic_square, age, gender, location_hometown, location_current 
        FROM Users WHERE user_id = ?
        """        

SQL_SEL_USER_AUTH = """
        SELECT user_id, device_id, device_token, device_type_id, active_status, badge_changed, new_message 
        FROM UserAuth WHERE user_id = ?
        """                        
        
SQL_UPD_ACTIVE_STATUS = """
        UPDATE UserAuth SET active_status = ?
        WHERE user_id = ? and device_id = ?
        """

SQL_UPD_USER = """
        UPDATE Users 
        SET first_name = ?, last_name = ?, user_pic = ?, user_pic_square= ?, update_time = utc_timestamp(), 
        birthday = ?, age = ?, gender = ?, location_hometown = ?, location_current = ?
        WHERE user_id = ?
        """
        
SQL_UPD_USER_MANAGEMENT = """
        UPDATE UserManagement 
        SET fb_token = ?, fb_token_status = ?
        WHERE user_id = ?
        """

SQL_SEL_FB_USER_BUNDLE = """
        SELECT fb_user_id, user_name, mail_name, user_pic_url
        FROM FacebookUsers WHERE fb_user_id in (%s)        
        """        
        
SQL_SEL_USER_BUNDLE = """
        SELECT user_id, fb_user_id, first_name, last_name, user_pic, user_pic_square, age, gender, location_hometown, location_current
        FROM Users WHERE user_id in (%s)        
        """

SQL_SEL_USER_MANAGEMENT_BUNDLE = """
        SELECT user_id, fb_user_id, fb_token, fb_token_status 
        FROM UserManagement WHERE fb_user_id in (%s)        
        """
        
SQL_INS_FB_FRIEND = """
        INSERT INTO FacebookFriends 
        (fb_user_id, fb_friend_id) 
        VALUES(?, ?) 
        """

# remove the record if the specified two persons are no longer friends
SQL_DEL_FB_FRIEND = """
        DELETE FROM FacebookFriends 
        WHERE fb_user_id = ? 
        AND fb_friend_id = ?
        """            
        
SQL_INS_FB_FRIEND_INFO = """
        INSERT INTO FacebookUsers 
        (fb_user_id, user_name, mail_name, user_pic_url) 
        VALUES(?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE update_time = utc_timestamp(), user_name = VALUES(user_name), mail_name = VALUES(mail_name), user_pic_url = VALUES(user_pic_url)
        """     

###################CreditCards############################
SQL_INS_CREDITCARD = """
        INSERT INTO BankCards 
        (card_number, owner_id, pin, valid_before, bind_status) 
        VALUES(?, ?, ?, ?, ?)        
        """
        
SQL_UPD_CREDITCARD_STATUS = """
        UPDATE BankCards SET bind_status = 0, update_time = utc_timestamp()
        WHERE card_id = ?        
        """        
        
SQL_SEL_CREDITCARD_WITH_NUMBUER = """
        SELECT card_id, card_number, owner_id, pin, valid_before, bind_status FROM BankCards 
        WHERE card_number = ?
        """

SQL_SEL_CREDITCARD_WITH_ID = """
        SELECT card_id, card_number, owner_id, pin, valid_before, bind_status FROM BankCards 
        WHERE card_id = ?
        """

###################Search############################
SQL_SEL_CREATED_VOUCHERS_OF_USER = """
        SELECT voucher_id, voucher_title, voucher_type, owner_id, receiver_id, create_date, expire_date,
                update_time, voucher_status, voucher_amount, voucher_remain, 
                loc_display_name, consume_type, date_type
        FROM Vouchers
        WHERE owner_id = ? 
        """
        
SQL_SEL_HISTORY_CREATED_VOUCHERS_OF_USER = """
        SELECT voucher_id, voucher_title, voucher_type, owner_id, receiver_id, create_date, expire_date,
                update_time, voucher_status, voucher_amount, voucher_remain, 
                loc_display_name, consume_type, date_type
        FROM Vouchers
        WHERE owner_id = ? and UNIX_TIMESTAMP(expire_date) <= utc_timestamp()
        """        
        
SQL_SEL_RECEIVED_VOUCHERS_OF_USER = """
        SELECT voucher_id, voucher_title, voucher_type, owner_id, receiver_id, create_date, expire_date,
                update_time, voucher_status, voucher_amount, voucher_remain, 
                loc_display_name, consume_type, date_type
        FROM Vouchers
        WHERE receiver_id = ? and voucher_status = ?
        """ 
           
SQL_SEL_ACCEPTED_VOUCHERS_OF_USER = """
        SELECT voucher_id, voucher_title, voucher_type, owner_id, receiver_id, create_date, expire_date,
                update_time, voucher_status, voucher_amount, voucher_remain, 
                loc_display_name, consume_type, date_type
        FROM Vouchers
        WHERE receiver_id = ? and voucher_status = ?
        """             

###################Vouchers############################
SQL_SEL_VOUCHERS="""
        SELECT voucher_id, voucher_title, voucher_type, owner_id, receiver_id, create_date, expire_date, start_date,
                update_time, voucher_status, voucher_amount, voucher_remain, 
                loc_left_top_longitude, loc_left_top_latitude, loc_right_bottom_longitude, loc_rigth_bottom_latitude,
                pin_code, loc_display_name, consume_type, date_type
        FROM Vouchers
        WHERE voucher_id = ?
        """

SQL_INS_VOUCHER = '''
        INSERT INTO Vouchers(voucher_title, voucher_type, owner_id, receiver_id, create_date, expire_date, start_date,
            update_time, voucher_status, voucher_amount, voucher_remain,
            loc_left_top_longitude, loc_left_top_latitude, loc_right_bottom_longitude, loc_rigth_bottom_latitude,
            pin_code, loc_display_name, consume_type, date_type, receiver_phone, receiver_email, receiver_fb_id)
        VALUES(?, ?, ?, ?, ?, ?, ?,
                ?, ?, ?, ?,
                ?, ?, ?, ?,
                ?, ?, ?, ?, ?, ?, ?)
        '''

SQL_UPDATE_VOUCHER = '''
        UPDATE Vouchers 
        SET voucher_title = ?, voucher_type = ?, receiver_id = ?, expire_date = ?, start_date = ?,
                update_time = utc_timestamp(), voucher_amount = ?,
                loc_left_top_longitude = ?, loc_left_top_latitude = ?, loc_right_bottom_longitude = ?, loc_rigth_bottom_latitude = ?,
                loc_display_name = ?, consume_type = ?, date_type = ? 
        WHERE voucher_id = ?
        '''

SQL_UPDATE_VOUCHER_STATUS= '''
        UPDATE Vouchers 
        SET voucher_status = ?, update_time = utc_timestamp() 
        WHERE voucher_id = ?
        '''

SQL_UPDATE_VOUCHER_RECEIVER= '''
        UPDATE Vouchers 
        SET receiver_id = ?, update_time = utc_timestamp() 
        WHERE voucher_id = ?
        '''


###################Transaction Part############################
SQL_INSERT_TRANSACTION = '''
    INSERT INTO Transactions( voucher_id, transaction_amount, transaction_status, transaction_date, store_name)
        VALUES(?, ?, ?, ?, ?)
'''

SQL_GET_TRANSACTION = '''
     SELECT voucher_id, transaction_id, transaction_amount, transaction_date, location_id, store_name 
        FROM Transactions 
        WHERE voucher_id = ? 
        ORDER BY transaction_date DESC

'''

UPDATE_VOUCHERS_REMAIN = ''' 
   UPDATE Vouchers SET voucher_remain = (voucher_amount - ?)  WHERE voucher_id = ?
'''

SQL_INS_LOCATION = '''
    INSERT INTO Locations(
    longitude, latitude, address, display_name, description,
    street, city, state, country)
    VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)
    '''
    
SQL_GET_LOCATION = '''
    SELECT location_id, longitude, latitude, address, display_name, description, street, city, state, country
        FROM Locations where location_id = ?

'''

SQL_GET_VOUCHER_PIN_CODE = '''
    SELECT pin_code from Vouchers where voucher_id = ?
'''

UPDATE_VOUCHER_PINCODE = '''
    UPDATE Vouchers SET pin_code = ?  WHERE voucher_id = ?
'''

CHECK_PINCODE_VALIDATE = '''
    SELECT v.voucher_id FROM PinCode p, Vouchers v
        WHERE p.pin_code = v.pin_code AND v.pin_code = ? 
        AND p.upd_time > date_sub(?, interval 2 minute)
'''


###################Utils Part############################
SQL_UPD_PIN = '''
    UPDATE PinCode SET upd_time = ? WHERE pin_code = ? AND upd_time < date_sub(?, interval 2 minute) 
    '''

SQL_EXPIRE_PIN = '''
    UPDATE PinCode SET upd_time = date_sub(upd_time, interval 3 minute) WHERE pin_code = ?
    '''
